from collections import defaultdict
from datetime import datetime

from flask import request, jsonify
from flask_restx import Namespace, Resource, fields
from flask_jwt_extended import jwt_required
from sqlalchemy import and_

# 导入 tb_alarm 模型
from app1.models import Zbfwb_RemedyGongdan
from app1 import db
from app1 import api

from app1.customdate import CustomDate
from app1.utils import format_response

# 创建 Flask-RESTx 命名空间
ns_remedy_gongdan = Namespace('nsremedygongdan', description='remedy 工单')

remedy_gongdan_model = ns_remedy_gongdan.model(
    'remedy_gongdan', {
        'TicketID': fields.String(description='工单编号'),
        'SRMType': fields.String(description='工单类型'),
        'Cer1': fields.String(description='证书1'),
        'Cer2': fields.String(description='证书2'),
        'Cer3': fields.String(description='证书3'),
        'Status': fields.String(description='状态'),
        'StatusReason': fields.String(description='状态原因'),
        'SystemName': fields.String(description='系统名称'),
        'Decription': fields.String(description='描述'),
        'SupportCompany': fields.String(description='支持公司'),
        'AssignedGroup': fields.String(description='分配组'),
        'Assignee': fields.String(description='分配人'),
        'Submiter': fields.String(description='提交人'),
        'Customer': fields.String(description='客户'),
        'ContactCompany': fields.String(description='联系公司'),
        'Organization': fields.String(description='组织'),
        'Department': fields.String(description='部门'),
        'CreateTime': CustomDate(dt_format='str_time', description='创建时间'),
        'SolveTime': CustomDate(dt_format='str_time', description='解决时间'),
        'RecTime': CustomDate(dt_format='str_time', description='接收时间'),
        'SendHx': fields.String(description='是否发送hx'),
    })

# 定义外层的响应模型
response_gongdan_model = ns_remedy_gongdan.model('Response_Gongdan', {
    'message': fields.Nested(ns_remedy_gongdan.model('Message_Gongdan', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(remedy_gongdan_model))
    }))
})

remedy_gongdan_multi_query_model = ns_remedy_gongdan.model(
    'remedy_gongdan_multi_query',
    {
        'SRMType': fields.String(description='工单类型', default=""),
        'Cer1': fields.String(description='证书1', default=""),
        'Cer2': fields.String(description='证书2', default=""),
        'Cer3': fields.String(description='证书3', default=""),
        'Status': fields.String(description='状态', default=""),
        'StatusReason': fields.String(description='状态原因', default=""),
        'SystemName': fields.String(description='系统名称', default=""),
        'SupportCompany': fields.String(description='支持公司', default=""),
        'AssignedGroup': fields.String(description='分配组', default=""),
        'Assignee': fields.String(description='分配人', default=""),
        'Submiter': fields.String(description='提交人', default=""),
        'Customer': fields.String(description='客户', default=""),
        'ContactCompany': fields.String(description='联系公司', default=""),
        'Create_start_date': fields.String(description='开始',
                                           default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
        'Create_end_date': fields.String(description='结束', default=str(datetime.now().strftime('%Y-%m-%d %H:%M:%S'))),
    }
)

# 定义地图响应的data数据
remedy_gongdan_map_model = ns_remedy_gongdan.model(
    'remedy_gongdan', {
        'name': fields.String(description='机构'),  #ContactCompany
        'value': fields.Integer(description='工单数量'),
        'SRMType': fields.String(description='工单类型'),
    })

# 定义地图响应数据
response_gongdan_map_model = ns_remedy_gongdan.model('Response_Gongdan_Map', {
    'message': fields.Nested(ns_remedy_gongdan.model('Message_Gongdan_Map', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(remedy_gongdan_map_model))
    }))
})


@ns_remedy_gongdan.route('/getbymultiquerylist')
class RemedyMultiQgongdanResource(Resource):

    @ns_remedy_gongdan.expect(remedy_gongdan_multi_query_model)
    # @jwt_required()
    @ns_remedy_gongdan.marshal_with(response_gongdan_model)
    def post(self):
        """多条件查询工单列表 日期格式：2024-06-18 18:10:13+点+247441"""  # 接口说明紧跟在函数后面
        remedydata = api.payload

        SRMType = remedydata['SRMType']
        Cer1 = remedydata['Cer1']
        Cer2 = remedydata['Cer2']
        Cer3 = remedydata['Cer3']
        Status = remedydata['Status']
        StatusReason = remedydata['StatusReason']
        SystemName = remedydata['SystemName']
        SupportCompany = remedydata['SupportCompany']
        AssignedGroup = remedydata['AssignedGroup']
        Assignee = remedydata['Assignee']
        Submiter = remedydata['Submiter']
        Customer = remedydata['Customer']
        ContactCompany = remedydata['ContactCompany']
        Create_start_date = remedydata['Create_start_date']
        Create_end_date = remedydata['Create_end_date']

        # 示例日期时间格式 2024-06-18 18:01:40.000000
        # 将日期字符串转换为 datetime 对象
        # c_start_date = datetime.strptime(Create_start_date, '%Y-%m-%d %H:%M:%S.%f')
        # c_end_date = datetime.strptime(Create_end_date, '%Y-%m-%d %H:%M:%S.%f')

        c_start_date = datetime.strptime(Create_start_date, '%Y-%m-%d %H:%M:%S')
        c_end_date = datetime.strptime(Create_end_date, '%Y-%m-%d %H:%M:%S')

        # 构建查询条件
        filters = [
            Zbfwb_RemedyGongdan.CreateTime >= c_start_date,
            Zbfwb_RemedyGongdan.CreateTime <= c_end_date
        ]

        if SRMType:
            filters.append(Zbfwb_RemedyGongdan.SRMType == SRMType)
        if Cer1:
            filters.append(Zbfwb_RemedyGongdan.Cer1 == Cer1)
        if Cer2:
            filters.append(Zbfwb_RemedyGongdan.Cer2 == Cer2)
        if Cer3:
            filters.append(Zbfwb_RemedyGongdan.Cer3 == Cer3)
        if Status:
            filters.append(Zbfwb_RemedyGongdan.Status == Status)
        if StatusReason:
            filters.append(Zbfwb_RemedyGongdan.StatusReason == StatusReason)
        if SystemName:
            filters.append(Zbfwb_RemedyGongdan.SystemName == SystemName)
        if SupportCompany:
            filters.append(Zbfwb_RemedyGongdan.SupportCompany == SupportCompany)
        if AssignedGroup:
            filters.append(Zbfwb_RemedyGongdan.AssignedGroup == AssignedGroup)
        if Assignee:
            filters.append(Zbfwb_RemedyGongdan.Assignee == Assignee)
        if Submiter:
            filters.append(Zbfwb_RemedyGongdan.Submiter == Submiter)
        if Customer:
            filters.append(Zbfwb_RemedyGongdan.Customer == Customer)
        if ContactCompany:
            filters.append(Zbfwb_RemedyGongdan.ContactCompany == ContactCompany)

        # 查询并过滤数据
        query = Zbfwb_RemedyGongdan.query.filter(and_(*filters))

        # 计算总记录数
        total_records = query.count()
        # 获取记录
        remedy_gongdan_data = query.all()

        if remedy_gongdan_data:
            # return kms_alarm
            # 使用 format_response 格式化返回结果
            response_data = format_response(remedy_gongdan_data, total_records, 1, "successfully")
            return response_data
        else:
            response_data = format_response("[]", 0, 0, "no data")
            return response_data


@ns_remedy_gongdan.route('/getbymultiquerymap')
class RemedyMultiQgongdanMapResource(Resource):

    @ns_remedy_gongdan.expect(remedy_gongdan_multi_query_model)
    # @jwt_required()
    @ns_remedy_gongdan.marshal_with(response_gongdan_map_model)
    def post(self):
        try:
            """多条件查询工单列表 日期格式：2024-06-18 18:10:13"""  # 接口说明紧跟在函数后面
            remedydata = api.payload

            SRMType = remedydata['SRMType']
            Cer1 = remedydata['Cer1']
            Cer2 = remedydata['Cer2']
            Cer3 = remedydata['Cer3']
            Status = remedydata['Status']
            StatusReason = remedydata['StatusReason']
            SystemName = remedydata['SystemName']
            SupportCompany = remedydata['SupportCompany']
            AssignedGroup = remedydata['AssignedGroup']
            Assignee = remedydata['Assignee']
            Submiter = remedydata['Submiter']
            Customer = remedydata['Customer']
            ContactCompany = remedydata['ContactCompany']
            Create_start_date = remedydata['Create_start_date']
            Create_end_date = remedydata['Create_end_date']

            # 示例日期时间格式 2024-06-18 18:01:40.000000
            # 将日期字符串转换为 datetime 对象
            # c_start_date = datetime.strptime(Create_start_date, '%Y-%m-%d %H:%M:%S.%f')
            # c_end_date = datetime.strptime(Create_end_date, '%Y-%m-%d %H:%M:%S.%f')

            c_start_date = datetime.strptime(Create_start_date, '%Y-%m-%d %H:%M:%S')
            c_end_date = datetime.strptime(Create_end_date, '%Y-%m-%d %H:%M:%S')

            # 构建查询条件
            filters = [
                Zbfwb_RemedyGongdan.CreateTime >= c_start_date,
                Zbfwb_RemedyGongdan.CreateTime <= c_end_date
            ]

            if SRMType:
                filters.append(Zbfwb_RemedyGongdan.SRMType == SRMType)
            if Cer1:
                filters.append(Zbfwb_RemedyGongdan.Cer1 == Cer1)
            if Cer2:
                filters.append(Zbfwb_RemedyGongdan.Cer2 == Cer2)
            if Cer3:
                filters.append(Zbfwb_RemedyGongdan.Cer3 == Cer3)
            if Status:
                filters.append(Zbfwb_RemedyGongdan.Status == Status)
            if StatusReason:
                filters.append(Zbfwb_RemedyGongdan.StatusReason == StatusReason)
            if SystemName:
                filters.append(Zbfwb_RemedyGongdan.SystemName == SystemName)
            if SupportCompany:
                filters.append(Zbfwb_RemedyGongdan.SupportCompany == SupportCompany)
            if AssignedGroup:
                filters.append(Zbfwb_RemedyGongdan.AssignedGroup == AssignedGroup)
            if Assignee:
                filters.append(Zbfwb_RemedyGongdan.Assignee == Assignee)
            if Submiter:
                filters.append(Zbfwb_RemedyGongdan.Submiter == Submiter)
            if Customer:
                filters.append(Zbfwb_RemedyGongdan.Customer == Customer)
            if ContactCompany:
                filters.append(Zbfwb_RemedyGongdan.ContactCompany == ContactCompany)

            # 查询并过滤数据
            query = Zbfwb_RemedyGongdan.query.filter(and_(*filters))

            # debug
            print(query.count())

            #####################
            # db.func.count(Zbfwb_RemedyGongdan.id).label('value')
            # 是
            # SQLAlchemy
            # 中的一种表达式，作用是对数据库中的
            # Zbfwb_RemedyGongdan
            # 表的
            # id
            # 字段进行计数操作，并为计数结果起一个别名（即
            # label）。
            #
            # 具体说明如下：
            #
            # 1.
            # db.func.count():
            # db.func
            # 是
            # SQLAlchemy
            # 用来生成数据库函数的对象，通过它可以调用
            # SQL
            # 中的各种函数。
            # count()
            # 是
            # SQL
            # 中的一个聚合函数，用来计算一列中的非空值数量。也就是计算
            # Zbfwb_RemedyGongdan.id
            # 列中有多少个非空记录。
            # 2.
            # Zbfwb_RemedyGongdan.id:
            # Zbfwb_RemedyGongdan
            # 是
            # SQLAlchemy
            # 模型类，表示数据库中的一张表。
            # id
            # 是这张表中的一个字段，通常作为主键字段，用来唯一标识每一条记录。
            # 3..label('value'):
            # label()
            # 用来给计算结果（即计数结果）起一个别名（alias）。
            # 在这个例子中，计数结果会被命名为
            # value，在查询结果中可以使用这个别名来引用计数的结果。
            # 完整理解：
            # 这一句代码的作用是统计
            # Zbfwb_RemedyGongdan
            # 表中每一条记录（根据
            # id
            # 字段来计数），然后将计数结果用
            # value
            # 作为别名。这个表达式通常用于查询中，特别是分组查询时，用于统计符合某些条件的记录数。

            #####################

            # 统计每个ContactCompany的数量
            # 按照 ContactCompany 和 SRMType 分组，统计每个组合的数量
            results = query.with_entities(
                Zbfwb_RemedyGongdan.ContactCompany.label('ContactCompany'),
                Zbfwb_RemedyGongdan.SRMType.label('SRMType'),
                db.func.count(Zbfwb_RemedyGongdan.TicketID).label('value')
            ).group_by(
                Zbfwb_RemedyGongdan.ContactCompany,
                Zbfwb_RemedyGongdan.SRMType
            ).all()

            # debug2
            print(results)

            # 构建响应数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "查询成功",
                    "count": len(results),
                    "data": [
                        {
                            "name": result.ContactCompany,
                            "SRMType": result.SRMType,
                            "value": result.value
                        }
                        for result in results
                    ]
                }
            }

            # 返回封装好的成功响应模型，状态码200
            return response_data, 200

        except Exception as e:
            # 捕获异常并准备异常响应数据
            response_data = {
                "message": {
                    "result": 0,  # 查询异常
                    "result_text": f"查询异常: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }

            # 返回封装好的异常响应模型，状态码201
            return response_data, 201
